﻿

CREATE OR REPLACE FUNCTION trg_contract_insert_change_completion_date()
  RETURNS trigger AS
$BODY$
	DECLARE
		using_id integer;
	Begin
                        -- ### Begin Update Trigger ###
		If TG_OP='UPDATE' then
			using_id = old.id;
			if old.completion_date is null and new.completion_date is null then
				return new;
			else
				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
												prj_id,
												max(kcc.completion_date) max_date
											from
												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
												union
												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
											left join
												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
											left join
												kdvn_Contract_client kcc on contract_id = kcc.id
											group by
												prj_id) vwtemp where kp.id =vwtemp.prj_id;
				return new;
			end if;
                elseif TG_OP='INSERT' then
			using_id = new.id;
			if new.completion_date is null then
				return new;
			else
				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
												prj_id,
												max(kcc.completion_date) max_date
											from
												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
												union
												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
											left join
												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
											left join
												kdvn_Contract_client kcc on contract_id = kcc.id
							
											group by
												prj_id) vwtemp where kp.id =vwtemp.prj_id;
				return new;
			end if;
		else
			using_id = old.id;
			if old.completion_date is null then
				return old;
			else
				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
												prj_id,
												max(kcc.completion_date) max_date
											from
												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
												union
												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
											left join
												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
											left join
												kdvn_Contract_client kcc on contract_id = kcc.id
											group by
												prj_id) vwtemp where kp.id =vwtemp.prj_id;
				return old;
			end if; 
		end if;-- END TG_OP
	end;    
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
-- 
CREATE TRIGGER trgchange_completion_date
  AFTER INSERT OR UPDATE OR DELETE
  ON kdvn_contract_client
  FOR EACH ROW
  EXECUTE PROCEDURE trg_contract_insert_change_completion_date();


-- 
-- -- No nedd
-- ---####### On Sale Order
-- CREATE OR REPLACE FUNCTION trg_contract_insert_change_completion_date_on_sale_order()
--   RETURNS trigger AS
-- $BODY$
-- 	DECLARE
-- 		using_id integer;
-- 	Begin
--                         -- ### Begin Update Trigger ###
-- 		If TG_OP='UPDATE' then
-- 			if old.contract_id is null and new.contract_id is not null then
-- 				using_id = new.contract_id;
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 			elseif old.contract_id is not null and new.contract_id is null then
-- 				using_id = old.contract_id;
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 			elseif old.contract_id is not null and new.contract_id is not null then
-- 				using_id = old.contract_id;
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 												
-- 				using_id = new.contract_id;
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 			end if;
-- 			return new;
--                 elseif TG_OP='INSERT' then
-- 			using_id =new.contract_id;
-- 			if new.contract_id is null then
-- 				return new;
-- 			else
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 				return new;
-- 			end if;
-- 		else
-- 			using_id = old.contract_id;
-- 			if old.contract_id is null then
-- 				return old;
-- 			else
-- 				Update kdvn_project kp set actual_completion_date=coalesce(max_date,null) from (Select 
-- 												prj_id,
-- 												max(kcc.completion_date) max_date
-- 											from
-- 												(Select project_e_id as prj_id from sale_order where coalesce(project_e_id,0)>0 and contract_id=using_id and state <>'cancel'
-- 												union
-- 												Select project_m_id as prj_id from sale_order where coalesce(project_m_id,0)>0 and contract_id=using_id and state <>'cancel') vwtemp
-- 											left join
-- 												sale_order so on prj_id = so.project_e_id or prj_id = so.project_m_id
-- 											left join
-- 												kdvn_Contract_client kcc on contract_id = kcc.id
-- 											group by
-- 												prj_id) vwtemp where kp.id =vwtemp.prj_id;
-- 				return old;
-- 			end if; 
-- 		end if;-- END TG_OP
-- 	end;    
-- 	$BODY$
--   LANGUAGE plpgsql VOLATILE
--   COST 100;
-- -- 
-- -- -- -- CREATE TRIGGER trgchange_completion_date_when_change_contract_id_sale_order
-- --   AFTER INSERT OR UPDATE OR DELETE
-- --   ON sale_order
-- --   FOR EACH ROW
-- --   EXECUTE PROCEDURE trg_contract_insert_change_completion_date_on_sale_order();
